package com.boss.thirteen.dao.impl;

import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.sys.utils.SysUtils;
import com.boss.thirteen.dao.ISalaryThirteenMonthDao;
import com.boss.thirteen.entity.SalaryThirteenMonth;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

@Repository
public class SalaryThirteenMonthEndDaoImpl extends BaseDaoImpl implements ISalaryThirteenMonthDao {
    @Override
    public List<SalaryThirteenMonth> querySalaryThirteenMonthPage(PaginationDTO paginationDTO, String agencyId, Map<String, Object> params) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select  a.id,  a.create_time,  a.update_time,  a.agency_id,  a.person_id,  a.is_thirteen,  a.mof_div_code, nvl(a.s1,0) s1, nvl(a.s2,0) s2, nvl(a.s3,0) s3, nvl(a.s4,0) s4, nvl(a.s5,0) s5, nvl(a.s6,0) s6, nvl(a.s7,0) s7, nvl(a.s8,0) s8, nvl(a.s9,0) s9, nvl(a.s10,0) s10, nvl(a.s11,0) s11, nvl(a.s12,0) s12, nvl(a.s13,0) s13, nvl(a.s14,0) s14, nvl(a.s15,0) s15, nvl(a.s16,0) s16, nvl(a.s17,0) s17, nvl(a.s18,0) s18, nvl(a.s19,0) s19, nvl(a.s20,0) s20, nvl(a.s21,0) s21, nvl(a.s22,0) s22, nvl(a.s23,0) s23, nvl(a.s24,0) s24, nvl(a.s25,0) s25, nvl(a.s26,0) s26, nvl(a.s27,0) s27, nvl(a.s28,0) s28, nvl(a.s29,0) s29, nvl(a.s30,0) s30, nvl(a.s31,0) s31, nvl(a.s32,0) s32, nvl(a.s33,0) s33, nvl(a.s34,0) s34, nvl(a.s35,0) s35, nvl(a.s36,0) s36, nvl(a.s37,0) s37, nvl(a.s38,0) s38, nvl(a.s39,0) s39, nvl(a.s40,0) s40, nvl(a.s41,0) s41, nvl(a.s42,0) s42, nvl(a.s43,0) s43, nvl(a.s44,0) s44, nvl(a.s45,0) s45, nvl(a.s46,0) s46, nvl(a.s47,0) s47, nvl(a.s48,0) s48, nvl(a.s49,0) s49, nvl(a.s50,0) s50, nvl(a.s51,0) s51, nvl(a.s52,0) s52, nvl(a.s53,0) s53, nvl(a.s54,0) s54, nvl(a.s55,0) s55, nvl(a.s56,0) s56, nvl(a.s57,0) s57, nvl(a.s58,0) s58, nvl(a.s59,0) s59, nvl(a.s60,0) s60, nvl(a.s61,0) s61, nvl(a.s62,0) s62, nvl(a.s63,0) s63, nvl(a.s64,0) s64, nvl(a.s65,0) s65, nvl(a.s66,0) s66, nvl(a.s67,0) s67, nvl(a.s68,0) s68, nvl(a.s69,0) s69, nvl(a.s70,0) s70, nvl(a.s71,0) s71, nvl(a.s72,0) s72, nvl(a.s73,0) s73, nvl(a.s74,0) s74, nvl(a.s75,0) s75, nvl(a.s76,0) s76, nvl(a.s77,0) s77, nvl(a.s78,0) s78, nvl(a.s79,0) s79, nvl(a.s80,0) s80, nvl(a.s81,0) s81, nvl(a.s82,0) s82, nvl(a.s83,0) s83, nvl(a.s84,0) s84, nvl(a.s85,0) s85, nvl(a.s86,0) s86, nvl(a.s87,0) s87, nvl(a.s88,0) s88, nvl(a.s89,0) s89, nvl(a.s90,0) s90, nvl(a.s91,0) s91, nvl(a.s92,0) s92, nvl(a.s93,0) s93, nvl(a.s94,0) s94, nvl(a.s95,0) s95, nvl(a.s96,0) s96, nvl(a.s97,0) s97, nvl(a.s98,0) s98, nvl(a.s99,0) s99, nvl(a.s100,0) s100, a.person_name,  a.iden_no,  a.sala_card_no,  a.sala_card_bank,  a.percent,  a.is_deleted,  a.merit_pay_code, b.id childId  from ")
                .append(TableName.SALARY_THIRTEEN_MONTH).append(" a ")
                .append(" left join ").append(TableName.SALARY_THIRTEEN_BEGIN).append(" b ")
                .append(" on  a.person_id=b.person_id and (a.s1<>b.s1 or a.s2<>b.s2 or a.s3<>b.s3 or a.s4<>b.s4 or a.s5<>b.s5 or a.s6<>b.s6 or a.s7<>b.s7 or a.s8<>b.s8 or a.s9<>b.s9 or a.s10<>b.s10 or a.s11<>b.s11 or a.s12<>b.s12 or a.s13<>b.s13 or a.s14<>b.s14 or a.s15<>b.s15 or a.s16<>b.s16 or a.s17<>b.s17 or a.s18<>b.s18 or a.s19<>b.s19 or a.s20<>b.s20 or a.s21<>b.s21 or a.s22<>b.s22 or a.s23<>b.s23 or a.s24<>b.s24 or a.s25<>b.s25 or a.s26<>b.s26 or a.s27<>b.s27 or a.s28<>b.s28 or a.s29<>b.s29 or a.s30<>b.s30 or a.s31<>b.s31 or a.s32<>b.s32 or a.s33<>b.s33 or a.s34<>b.s34 or a.s35<>b.s35 or a.s36<>b.s36 or a.s37<>b.s37 or a.s38<>b.s38 or a.s39<>b.s39 or a.s40<>b.s40 or a.s41<>b.s41 or a.s42<>b.s42 or a.s43<>b.s43 or a.s44<>b.s44 or a.s45<>b.s45 or a.s46<>b.s46 or a.s47<>b.s47 or a.s48<>b.s48 or a.s49<>b.s49 or a.s50<>b.s50 or a.s51<>b.s51 or a.s52<>b.s52 or a.s53<>b.s53 or a.s54<>b.s54 or a.s55<>b.s55 or a.s56<>b.s56 or a.s57<>b.s57 or a.s58<>b.s58 or a.s59<>b.s59 or a.s60<>b.s60 or a.s61<>b.s61 or a.s62<>b.s62 or a.s63<>b.s63 or a.s64<>b.s64 or a.s65<>b.s65 or a.s66<>b.s66 or a.s67<>b.s67 or a.s68<>b.s68 or a.s69<>b.s69 or a.s70<>b.s70 or a.s71<>b.s71 or a.s72<>b.s72 or a.s73<>b.s73 or a.s74<>b.s74 or a.s75<>b.s75 or a.s76<>b.s76 or a.s77<>b.s77 or a.s78<>b.s78 or a.s79<>b.s79 or a.s80<>b.s80 or a.s81<>b.s81 or a.s82<>b.s82 or a.s83<>b.s83 or a.s84<>b.s84 or a.s85<>b.s85 or a.s86<>b.s86 or a.s87<>b.s87 or a.s88<>b.s88 or a.s89<>b.s89 or a.s90<>b.s90 or a.s91<>b.s91 or a.s92<>b.s92 or a.s93<>b.s93 or a.s94<>b.s94 or a.s95<>b.s95 or a.s96<>b.s96 or a.s97<>b.s97 or a.s98<>b.s98 or a.s99<>b.s99 or a.s100<>b.s100 or a.person_name<>b.person_name or a.iden_no<>b.iden_no or a.sala_card_no<>b.sala_card_no or a.sala_card_bank<>b.sala_card_bank or a.percent<>b.percent or a.is_deleted<>b.is_deleted or a.merit_pay_code<>b.merit_pay_code)")
                .append(" where a.agency_id=? ");
        paramList.add(agencyId);
        Object personName = params.get("personName");
        if (!StringUtils.isEmpty(personName)) {
            sb.append(" and a.person_name like concat('%',?,'%') ");
            paramList.add(String.valueOf(personName));
        }

        Object idenNo = params.get("idenNo");
        if (!StringUtils.isEmpty(idenNo)) {
            sb.append(" and a.iden_no like concat('%',?,'%') ");
            paramList.add(String.valueOf(idenNo));
        }
        Object salaryType = params.get("salaryType");
        if (!StringUtils.isEmpty(salaryType)) {
            sb.append(" and a.merit_pay_code = ?");
            paramList.add(String.valueOf(salaryType));
        }
        sb.append(" order by b.id desc,a.iden_no ");
        String querySql = super.boxUpSql(sb.toString());
        //请求参数初始化
        Object[] objects = super.boxUpParam(paramList.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(querySql,new BeanPropertyRowMapper(SalaryThirteenMonth.class), objects);

    }




    @Override
    public int querySalaryThirteenMonthCount(String agencyId, Map<String, Object> params) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select count(*) from ")
                .append(TableName.SALARY_THIRTEEN_MONTH)
                .append(" where agency_id=?");
        paramList.add(agencyId);
        Object personName = params.get("personName");
        if (!StringUtils.isEmpty(personName)) {
            sb.append(" and person_name like concat('%',?,'%') ");
            paramList.add(String.valueOf(personName));
        }

        Object idenNo = params.get("idenNo");
        if (!StringUtils.isEmpty(idenNo)) {
            sb.append(" and iden_no like concat('%',?,'%') ");
            paramList.add(String.valueOf(idenNo));
        }
        Object salaryType = params.get("salaryType");
        if (!StringUtils.isEmpty(salaryType)) {
            sb.append(" and merit_pay_code = ?");
            paramList.add(String.valueOf(salaryType));
        }
        return  this.getJdbcTemplate().queryForObject(sb.toString(),paramList.toArray(), Integer.class);
    }
    @Override
    public List<Map<String,Object>> selecctSalaryThirteenMonth( String agencyId) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select id, create_time, update_time, agency_id, person_id, mof_div_code,  person_name, iden_no, sala_card_no, sala_card_bank, percent, is_deleted, merit_pay_code,")
                .append(" nvl(s1,0) s1 ,nvl(s2,0) s2 ,nvl(s3,0) s3 ,nvl(s4,0) s4 ,nvl(s5,0) s5 ,nvl(s6,0) s6 ,nvl(s7,0) s7 ,nvl(s8,0) s8 ,nvl(s9,0) s9 ,nvl(s10 ,0) s10,nvl(s11 ,0) s11,nvl(s12 ,0) s12,nvl(s13 ,0) s13,nvl(s14 ,0) s14,nvl(s15 ,0) s15,nvl(s16 ,0) s16,nvl(s17 ,0) s17,nvl(s18 ,0) s18,nvl(s19 ,0) s19,nvl(s20 ,0) s20,nvl(s21 ,0) s21,nvl(s22 ,0) s22,nvl(s23 ,0) s23,nvl(s24 ,0) s24,nvl(s25 ,0) s25,nvl(s26 ,0) s26,nvl(s27 ,0) s27,nvl(s28 ,0) s28,nvl(s29 ,0) s29,nvl(s30 ,0) s30,nvl(s31 ,0) s31,nvl(s32 ,0) s32,nvl(s33 ,0) s33,nvl(s34 ,0) s34,nvl(s35 ,0) s35,nvl(s36 ,0) s36,nvl(s37 ,0) s37,nvl(s38 ,0) s38,nvl(s39 ,0) s39,nvl(s40 ,0) s40,nvl(s41 ,0) s41,nvl(s42 ,0) s42,nvl(s43 ,0) s43,nvl(s44 ,0) s44,nvl(s45 ,0) s45,nvl(s46 ,0) s46,nvl(s47 ,0) s47,nvl(s48 ,0) s48,nvl(s49 ,0) s49,nvl(s50 ,0) s50,nvl(s51 ,0) s51,nvl(s52 ,0) s52,nvl(s53 ,0) s53,nvl(s54 ,0) s54,nvl(s55 ,0) s55,nvl(s56 ,0) s56,nvl(s57 ,0) s57,nvl(s58 ,0) s58,nvl(s59 ,0) s59,nvl(s60 ,0) s60,nvl(s61 ,0) s61,nvl(s62 ,0) s62,nvl(s63 ,0) s63,nvl(s64 ,0) s64,nvl(s65 ,0) s65,nvl(s66 ,0) s66,nvl(s67 ,0) s67,nvl(s68 ,0) s68,nvl(s69 ,0) s69,nvl(s70 ,0) s70,nvl(s71 ,0) s71,nvl(s72 ,0) s72,nvl(s73 ,0) s73,nvl(s74 ,0) s74,nvl(s75 ,0) s75,nvl(s76 ,0) s76,nvl(s77 ,0) s77,nvl(s78 ,0) s78,nvl(s79 ,0) s79,nvl(s80 ,0) s80,nvl(s81 ,0) s81,nvl(s82 ,0) s82,nvl(s83 ,0) s83,nvl(s84 ,0) s84,nvl(s85 ,0) s85,nvl(s86 ,0) s86,nvl(s87 ,0) s87,nvl(s88 ,0) s88,nvl(s89 ,0) s89,nvl(s90 ,0) s90,nvl(s91 ,0) s91,nvl(s92 ,0) s92,nvl(s93 ,0) s93,nvl(s94 ,0) s94,nvl(s95 ,0) s95,nvl(s96 ,0) s96,nvl(s97 ,0) s97,nvl(s98 ,0) s98,nvl(s99 ,0) s99,nvl(s100,0) s100")
                .append(" from ")
                .append(TableName.SALARY_THIRTEEN_MONTH)
                .append(" where agency_id=? ");
        paramList.add(agencyId);
        sb.append(" order by iden_no ");
        return super.getjdbcTemplate().queryForList(sb.toString(),paramList.toArray());

    }

    @Override
    public List<SalaryThirteenMonth> selecctSalaryThirteenBeginById( List<String> ids) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select id, create_time, update_time, agency_id, person_id, mof_div_code,  person_name, iden_no, sala_card_no, sala_card_bank, percent, is_deleted, merit_pay_code,")
                .append(" nvl(s1,0) s1 ,nvl(s2,0) s2 ,nvl(s3,0) s3 ,nvl(s4,0) s4 ,nvl(s5,0) s5 ,nvl(s6,0) s6 ,nvl(s7,0) s7 ,nvl(s8,0) s8 ,nvl(s9,0) s9 ,nvl(s10 ,0) s10,nvl(s11 ,0) s11,nvl(s12 ,0) s12,nvl(s13 ,0) s13,nvl(s14 ,0) s14,nvl(s15 ,0) s15,nvl(s16 ,0) s16,nvl(s17 ,0) s17,nvl(s18 ,0) s18,nvl(s19 ,0) s19,nvl(s20 ,0) s20,nvl(s21 ,0) s21,nvl(s22 ,0) s22,nvl(s23 ,0) s23,nvl(s24 ,0) s24,nvl(s25 ,0) s25,nvl(s26 ,0) s26,nvl(s27 ,0) s27,nvl(s28 ,0) s28,nvl(s29 ,0) s29,nvl(s30 ,0) s30,nvl(s31 ,0) s31,nvl(s32 ,0) s32,nvl(s33 ,0) s33,nvl(s34 ,0) s34,nvl(s35 ,0) s35,nvl(s36 ,0) s36,nvl(s37 ,0) s37,nvl(s38 ,0) s38,nvl(s39 ,0) s39,nvl(s40 ,0) s40,nvl(s41 ,0) s41,nvl(s42 ,0) s42,nvl(s43 ,0) s43,nvl(s44 ,0) s44,nvl(s45 ,0) s45,nvl(s46 ,0) s46,nvl(s47 ,0) s47,nvl(s48 ,0) s48,nvl(s49 ,0) s49,nvl(s50 ,0) s50,nvl(s51 ,0) s51,nvl(s52 ,0) s52,nvl(s53 ,0) s53,nvl(s54 ,0) s54,nvl(s55 ,0) s55,nvl(s56 ,0) s56,nvl(s57 ,0) s57,nvl(s58 ,0) s58,nvl(s59 ,0) s59,nvl(s60 ,0) s60,nvl(s61 ,0) s61,nvl(s62 ,0) s62,nvl(s63 ,0) s63,nvl(s64 ,0) s64,nvl(s65 ,0) s65,nvl(s66 ,0) s66,nvl(s67 ,0) s67,nvl(s68 ,0) s68,nvl(s69 ,0) s69,nvl(s70 ,0) s70,nvl(s71 ,0) s71,nvl(s72 ,0) s72,nvl(s73 ,0) s73,nvl(s74 ,0) s74,nvl(s75 ,0) s75,nvl(s76 ,0) s76,nvl(s77 ,0) s77,nvl(s78 ,0) s78,nvl(s79 ,0) s79,nvl(s80 ,0) s80,nvl(s81 ,0) s81,nvl(s82 ,0) s82,nvl(s83 ,0) s83,nvl(s84 ,0) s84,nvl(s85 ,0) s85,nvl(s86 ,0) s86,nvl(s87 ,0) s87,nvl(s88 ,0) s88,nvl(s89 ,0) s89,nvl(s90 ,0) s90,nvl(s91 ,0) s91,nvl(s92 ,0) s92,nvl(s93 ,0) s93,nvl(s94 ,0) s94,nvl(s95 ,0) s95,nvl(s96 ,0) s96,nvl(s97 ,0) s97,nvl(s98 ,0) s98,nvl(s99 ,0) s99,nvl(s100,0) s100")
                .append(" from ")
                .append(TableName.SALARY_THIRTEEN_BEGIN)
                .append(" where  ").append(SysUtils.inSql("id",ids));
        return this.getJdbcTemplate().query(sb.toString(),new BeanPropertyRowMapper(SalaryThirteenMonth.class));

    }

    @Override
    public List<Map<String,Object>> selectVerifySalaryData(PaginationDTO paginationDTO, String whereSql,List<String> agencyIds,Map<String,Object> otherParams) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" count(stm.id) personCount,sum(s1) s1,av.biz_key bizKey,av.is_salary_submit isSalarySubmit,av.mould_key mouldKey,av.agency_code,av.agency_name,av.salary_year year,av.agency_id,is_pay_data_created status,is_end,salary_info_verified_code verifyStatus ")
                .append(" from ")
                .append(TableName.SALARY_THIRTEEN_MONTH)
                .append(" stm right  join ").append(TableName.THIRTEEN_AGENCY_VERIFY).append(" av ")
                .append(" on stm.agency_id=av.agency_id ");
//                .append(" where av.is_end=? and av.is_deleted=? ").append(whereSql)
//                .append(" and ").append(SysUtils.inSql("av.agency_id",agencyIds));//权限控制
//        paramList.add(SystemDictConstant.IS_FALSE);
//        paramList.add(SystemDictConstant.NOT_DELETED);
        if(!SysUtils.isNullOrEmpty(otherParams.get("bank"))){
            sb.append(" and stm.sala_card_bank=? ");
            paramList.add(otherParams.get("bank"));
        }
        if(!SysUtils.isNullOrEmpty(otherParams.get("province"))){
            sb.append(" and av.mof_div_code=? ");
            paramList.add(otherParams.get("province"));
        }
        if(!SysUtils.isNullOrEmpty(otherParams.get("is_pay_data_created"))){
            sb.append(" and av.is_pay_data_created=? ");
            paramList.add(otherParams.get("is_pay_data_created"));
        }
        sb.append(" group by av.biz_key ,av.is_salary_submit ,av.mould_key ,av.agency_code,av.agency_name,av.salary_year ,av.agency_id,is_pay_data_created ,is_end,salary_info_verified_code   ");
        sb.append(" order by av.agency_code ");
        String querySql = super.boxUpSql(sb.toString());
        //请求参数初始化
        Object[] objects = super.boxUpParam(paramList.toArray(), paginationDTO);
        return super.getjdbcTemplate().queryForList(querySql,objects);
    }

    @Override
    public int selectVerifySalaryDataTotal( String whereSql,List<String> agencyIds,Map<String,Object> otherParams) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" count(*) ")
                .append(" from ").append(TableName.THIRTEEN_AGENCY_VERIFY).append(" av ");
//                .append(" where is_end=? and is_deleted=? ").append(whereSql)
//                .append(" and ").append(SysUtils.inSql("agency_id",agencyIds));//权限控制
//        paramList.add(SystemDictConstant.IS_FALSE);
//        paramList.add(SystemDictConstant.NOT_DELETED);
//        if(!SysUtils.isNullOrEmpty(otherParams.get("bank"))){
//            sb.append(" and agency_id in ( select distinct agency_id from ")
//                    .append(TableName.SALARY_THIRTEEN_MONTH)
//                    .append(" where sala_card_bank=? and mof_div_code=? )");
//            paramList.add(otherParams.get("bank"));
//            paramList.add(otherParams.get("province"));
//        }
//        if(!SysUtils.isNullOrEmpty(otherParams.get("province"))){
//            sb.append(" and av.mof_div_code=? ");
//            paramList.add(otherParams.get("province"));
//        }
//        if(!SysUtils.isNullOrEmpty(otherParams.get("is_pay_data_created"))){
//            sb.append(" and av.is_pay_data_created=? ");
//            paramList.add(otherParams.get("is_pay_data_created"));
//        }
        return  this.getJdbcTemplate().queryForObject(sb.toString(),paramList.toArray(), Integer.class);
    }
    @Override
    public List<SalaryThirteenMonth> selectSalaryThirteenMonth(Map<String, Object> params) {

        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select id, create_time, update_time, agency_id, person_id, mof_div_code,  person_name, iden_no, sala_card_no, sala_card_bank, percent, is_deleted, merit_pay_code,")
                .append(" nvl(s1,0) s1 ,nvl(s2,0) s2 ,nvl(s3,0) s3 ,nvl(s4,0) s4 ,nvl(s5,0) s5 ,nvl(s6,0) s6 ,nvl(s7,0) s7 ,nvl(s8,0) s8 ,nvl(s9,0) s9 ,nvl(s10 ,0) s10,nvl(s11 ,0) s11,nvl(s12 ,0) s12,nvl(s13 ,0) s13,nvl(s14 ,0) s14,nvl(s15 ,0) s15,nvl(s16 ,0) s16,nvl(s17 ,0) s17,nvl(s18 ,0) s18,nvl(s19 ,0) s19,nvl(s20 ,0) s20,nvl(s21 ,0) s21,nvl(s22 ,0) s22,nvl(s23 ,0) s23,nvl(s24 ,0) s24,nvl(s25 ,0) s25,nvl(s26 ,0) s26,nvl(s27 ,0) s27,nvl(s28 ,0) s28,nvl(s29 ,0) s29,nvl(s30 ,0) s30,nvl(s31 ,0) s31,nvl(s32 ,0) s32,nvl(s33 ,0) s33,nvl(s34 ,0) s34,nvl(s35 ,0) s35,nvl(s36 ,0) s36,nvl(s37 ,0) s37,nvl(s38 ,0) s38,nvl(s39 ,0) s39,nvl(s40 ,0) s40,nvl(s41 ,0) s41,nvl(s42 ,0) s42,nvl(s43 ,0) s43,nvl(s44 ,0) s44,nvl(s45 ,0) s45,nvl(s46 ,0) s46,nvl(s47 ,0) s47,nvl(s48 ,0) s48,nvl(s49 ,0) s49,nvl(s50 ,0) s50,nvl(s51 ,0) s51,nvl(s52 ,0) s52,nvl(s53 ,0) s53,nvl(s54 ,0) s54,nvl(s55 ,0) s55,nvl(s56 ,0) s56,nvl(s57 ,0) s57,nvl(s58 ,0) s58,nvl(s59 ,0) s59,nvl(s60 ,0) s60,nvl(s61 ,0) s61,nvl(s62 ,0) s62,nvl(s63 ,0) s63,nvl(s64 ,0) s64,nvl(s65 ,0) s65,nvl(s66 ,0) s66,nvl(s67 ,0) s67,nvl(s68 ,0) s68,nvl(s69 ,0) s69,nvl(s70 ,0) s70,nvl(s71 ,0) s71,nvl(s72 ,0) s72,nvl(s73 ,0) s73,nvl(s74 ,0) s74,nvl(s75 ,0) s75,nvl(s76 ,0) s76,nvl(s77 ,0) s77,nvl(s78 ,0) s78,nvl(s79 ,0) s79,nvl(s80 ,0) s80,nvl(s81 ,0) s81,nvl(s82 ,0) s82,nvl(s83 ,0) s83,nvl(s84 ,0) s84,nvl(s85 ,0) s85,nvl(s86 ,0) s86,nvl(s87 ,0) s87,nvl(s88 ,0) s88,nvl(s89 ,0) s89,nvl(s90 ,0) s90,nvl(s91 ,0) s91,nvl(s92 ,0) s92,nvl(s93 ,0) s93,nvl(s94 ,0) s94,nvl(s95 ,0) s95,nvl(s96 ,0) s96,nvl(s97 ,0) s97,nvl(s98 ,0) s98,nvl(s99 ,0) s99,nvl(s100,0) s100")
                .append(" from ")
                .append(TableName.SALARY_THIRTEEN_MONTH)
                .append(" where mof_div_code=? ");
        paramList.add(params.get("province"));
        if(!SysUtils.isNullOrEmpty(params.get("agencyId"))) {
            sb.append(" and agency_id=? ");
            paramList.add(params.get("agencyId"));
        }
        sb.append(" order by iden_no ");
        return this.getJdbcTemplate().query(sb.toString(),new BeanPropertyRowMapper(SalaryThirteenMonth.class), paramList.toArray());
    }

    @Override
    public void batchInsertSalaryThirteenMonth(List<SalaryThirteenMonth> list) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        String time=String.valueOf(DateUtils.getCurrentTimeStamp());
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.SALARY_THIRTEEN_MONTH);
        sql.append("(id, create_time, update_time, agency_id, person_id, mof_div_code,is_thirteen, ")
                .append(" person_name, iden_no, sala_card_no, sala_card_bank, percent, is_deleted, merit_pay_code ")
                .append(" , s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, s39, s40, s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, s57, s58, s59, s60, s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, s75, s76, s77, s78, s79, s80, s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, s93, s94, s95, s96, s97, s98, s99, s100)");
        sql.append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        List<Object[]> paramList = new ArrayList<>(list.size());
        for(SalaryThirteenMonth stm : list){
            List<Object> param=new ArrayList<>();
            param.add(SysUtils.generateGuid());
            param.add(time);
            param.add(time);
            param.add(stm.getAgencyId());
            param.add(stm.getPersonId());
            param.add(stm.getMofDivCode());
            param.add(SystemDictConstant.IS_TRUE);
            param.add(stm.getPersonName());
            param.add(stm.getIdenNo());
            param.add(stm.getSalaCardNo());
            param.add(stm.getSalaCardBank());
            param.add(stm.getPercent());
            param.add(SystemDictConstant.NOT_DELETED);
            param.add(stm.getMeritPayCode());
            for (int i=1;i<=100;i++) {
                Class<?> clazz = stm.getClass();
                Method getMethod = clazz.getDeclaredMethod("getS" +i
                        );
                Object ob=SysUtils.isNotNullOrEmpty(getMethod.invoke(stm))?getMethod.invoke(stm):"0";
                param.add(ob);
            }

            paramList.add(param.toArray());
        }

        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramList.get(i);
                for(int a = 0; a < sme.length; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }


    @Override
    public void batchInsertSalaryThirteenBegin(List<SalaryThirteenMonth> list) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        String salary_year= String.valueOf(Calendar.getInstance().get(Calendar.YEAR));
        String time=String.valueOf(DateUtils.getCurrentTimeStamp());
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.SALARY_THIRTEEN_BEGIN);
        sql.append("(id, create_time, update_time, agency_id, person_id, mof_div_code, ")
                .append(" person_name, iden_no, sala_card_no, sala_card_bank, percent, is_deleted, merit_pay_code,salary_year, ")
                .append("  s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, s39, s40, s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, s57, s58, s59, s60, s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, s75, s76, s77, s78, s79, s80, s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, s93, s94, s95, s96, s97, s98, s99, s100)");
        sql.append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        List<Object[]> paramList = new ArrayList<>(list.size());
        for(SalaryThirteenMonth stm : list){
            List<Object> param=new ArrayList<>();
            param.add(SysUtils.generateGuid());
            param.add(time);
            param.add(time);
            param.add(stm.getAgencyId());
            param.add(stm.getPersonId());
            param.add(stm.getMofDivCode());
            param.add(stm.getPersonName());
            param.add(stm.getIdenNo());
            param.add(stm.getSalaCardNo());
            param.add(stm.getSalaCardBank());
            param.add(stm.getPercent());
            param.add(SystemDictConstant.NOT_DELETED);
            param.add(stm.getMeritPayCode());
            param.add(salary_year);
            for (int i=1;i<=100;i++) {
                Class<?> clazz = stm.getClass();
                Method getMethod = clazz.getDeclaredMethod("getS" +i
                );
                Object ob=SysUtils.isNotNullOrEmpty(getMethod.invoke(stm))?getMethod.invoke(stm):"0";
                param.add(ob);
            }
            paramList.add(param.toArray());
        }

        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramList.get(i);
                for(int a = 0; a < sme.length; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void batchUpdateSalaryThirteenMonth(List<SalaryThirteenMonth> list) throws Exception {
        String time=String.valueOf(DateUtils.getCurrentTimeStamp());
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_THIRTEEN_MONTH);
        sql.append(" set update_time=?, ")
                .append(" sala_card_no=?, sala_card_bank=? ");

        List<Object[]> paramList = new ArrayList<>(list.size());
        for (int i=1;i<=100;i++) {
            sql.append(",s").append(i).append("=? ");
        }
        sql.append(" where id=? ");
        for(SalaryThirteenMonth stm : list){
            List<Object> param=new ArrayList<>();
            param.add(time);
            param.add(stm.getSalaCardNo());
            param.add(stm.getSalaCardBank());
            for (int i=1;i<=100;i++) {
                Class<?> clazz = stm.getClass();
                Method getMethod = clazz.getDeclaredMethod("getS" +i
                );
                Object ob=SysUtils.isNotNullOrEmpty(getMethod.invoke(stm))?getMethod.invoke(stm):"0";
                param.add(ob);
            }
            param.add(stm.getId());
            paramList.add(param.toArray());
        }

        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramList.get(i);
                for(int a = 0; a < sme.length; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void updateSalaryThirteenMonth(SalaryThirteenMonth stm) throws Exception {
        String time=String.valueOf(DateUtils.getCurrentTimeStamp());
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_THIRTEEN_MONTH);
        sql.append(" set update_time=?, ")
                .append(" sala_card_no=?, sala_card_bank=? ");
        for (int i=1;i<=100;i++) {
            sql.append(",s").append(i).append("=? ");
        }
        sql.append(" where id=? ");
         List<Object> param=new ArrayList<>();
         param.add(time);
         param.add(stm.getSalaCardNo());
         param.add(stm.getSalaCardBank());
         for (int i=1;i<=100;i++) {
             Class<?> clazz = stm.getClass();
             Method getMethod = clazz.getDeclaredMethod("getS" +i
             );
             Object ob=SysUtils.isNotNullOrEmpty(getMethod.invoke(stm))?getMethod.invoke(stm):"0";
             param.add(ob);
         }
         param.add(stm.getId());

        super.getJdbcTemplate().update(sql.toString(),param.toArray());
    }

    @Override
    public void deleteSalaryThirteenMonth(List<String> ids) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete from ").append(TableName.SALARY_THIRTEEN_MONTH).append(" where ").append(SysUtils.inSql("id",ids));
        super.getJdbcTemplate().update(sql.toString());
    }

    @Override
    public List<SalaryThirteenMonth> selectSalaryThirteenMonthFromHistory(List<String> agencyId, String salaryMonth,String meritPayCode,List<String>
            columns,Double percent) {
        List<Object> param=new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select  ").append(" a.id, a.create_time, a.update_time, a.agency_id, a.person_id, a.mof_div_code, ")
                .append(" b.person_name, b.iden_no, b.sala_card_no, b.sala_card_bank, b.merit_pay_code ,")
                .append(percent).append(" percent ");
        for(String column:columns){
            sql.append(",").append(column);
        }
        sql.append(" from ").append(TableName.SALARY_HISTORY).append(" a ")
                .append(" join ").append(TableName.PERSON_EXT).append(" b ")
                .append(" on a.person_id=b.person_id ")
                .append(" where ").append(SysUtils.inSql(" a.agency_id ",agencyId))
                .append(" and a.salary_month = ? and b.merit_pay_code=? ");
        param.add(salaryMonth);
        param.add(meritPayCode);
        return this.getJdbcTemplate().query(sql.toString(),new BeanPropertyRowMapper(SalaryThirteenMonth.class),param.toArray());
    }

    @Override
    public List<SalaryThirteenMonth> selectSalaryThirteenPersonFromHistory(String agencyId, String salary_year,String province) {
        List<Object> param=new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select  ").append(" b.agency_id, a.person_id, b.mof_div_code, ")
                .append(" b.person_name, b.iden_no, b.sala_card_no, b.sala_card_bank, b.merit_pay_code ,")
                .append(" c.percent ");
        sql.append(" from (select distinct person_id from ").append(TableName.SALARY_HISTORY).append(" where agency_id=?  and left(salary_month,4)= ? )").append(" a ")
                .append(" join ").append(TableName.PERSON_EXT).append(" b ")
                .append(" on a.person_id=b.person_id ")
                .append("join ").append(TableName.THIRTEEN_MOULD).append(" c on b.mof_div_code=c.mof_div_code and c.merit_pay_code=b.merit_pay_code")
                .append(" where a.person_id not in (select person_id from ")
                .append(TableName.SALARY_THIRTEEN_MONTH).append(" where mof_div_code=? ) ")
                .append(" order by b.iden_no ");
        param.add(agencyId);
        param.add(salary_year);
        param.add(province);
        return this.getJdbcTemplate().query(sql.toString(),new BeanPropertyRowMapper(SalaryThirteenMonth.class),param.toArray());
    }


    @Override
    public List<Map<String, Object>> selectMXOffer(String mof_div_code, List<String> columnList) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" select ")
                .append(" smed.sala_card_bank salaCardBank, ")
                .append(" avy.agency_code agencyCode, ")
                .append(" avy.agency_name agencyName, ")
                .append(" if(smed.merit_pay_code is null or smed.merit_pay_code = '',0,smed.merit_pay_code) meritPayCode, ")
                .append(" smed.sala_card_no salaCardNo, ")
                .append(" smed.person_name personName, ")
                .append(" smed.iden_no idenNo ");
        if(!CollectionUtils.isEmpty(columnList)){
            for(String column : columnList){
                if(!"0".equals(column)){
                    sb.append(" , if(smed.").append(column).append(" is null ")
                            .append(" or smed.").append(column).append(" = 0, '0', smed.").append(column)
                            .append(") ").append(column);
                }
            }
        }
        sb.append(" from ").append(TableName.SALARY_THIRTEEN_MONTH).append(" smed ")
                .append(" join ").append(TableName.THIRTEEN_AGENCY_VERIFY).append(" avy ")
                .append(" on ").append(" avy.agency_id = smed.agency_id and avy.is_deleted = 0 ")
                .append(" and avy.is_bank_data_created = 0 and avy.is_end = 0 and is_pay_data_created = 1")
                .append(" where smed.mof_div_code=? ");
        params.add(mof_div_code);
        return super.getjdbcTemplate().queryForList(sb.toString(), params.toArray());
    }

    @Override
    public List<Map<String,Object>> selectVerifySalaryHisData(PaginationDTO paginationDTO, String whereSql,List<String> agencyIds,Map<String,Object> otherParams) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" count(stm.id) personCount,sum(s1) s1,av.biz_key bizKey,av.mould_key mouldKey,av.agency_code,av.agency_name,av.salary_year year,av.agency_id agencyId,is_pay_data_created status,is_end ")
                .append(" from ")
                .append(TableName.SALARY_THIRTEEN_MONTH_HIS)
                .append(" stm right join ").append(TableName.THIRTEEN_AGENCY_VERIFY).append(" av ")
                .append(" on stm.agency_id=av.agency_id and stm.salary_year=av.salary_year ")
                .append(" where av.is_end=? and av.is_deleted=? ").append(whereSql)
                .append(" and ").append(SysUtils.inSql("av.agency_id",agencyIds));//权限控制
        paramList.add(SystemDictConstant.IS_TRUE);
        paramList.add(SystemDictConstant.NOT_DELETED);
        if(!SysUtils.isNullOrEmpty(otherParams.get("bank"))){
            sb.append(" and stm.sala_card_bank=? ");
            paramList.add(otherParams.get("bank"));
        }
        if(!SysUtils.isNullOrEmpty(otherParams.get("province"))){
            sb.append(" and av.mof_div_code=? ");
            paramList.add(otherParams.get("province"));
        }
        if(!SysUtils.isNullOrEmpty(otherParams.get("is_pay_data_created"))){
            sb.append(" and av.is_pay_data_created=? ");
            paramList.add(otherParams.get("is_pay_data_created"));
        }
        if(!SysUtils.isNullOrEmpty(otherParams.get("year"))){
            sb.append(" and stm.salary_year=? ");
            paramList.add(otherParams.get("year"));
        }
        sb.append(" group by av.agency_code,av.agency_name,av.salary_year,av.biz_key,av.agency_id,is_pay_data_created,is_end ");
        sb.append(" order by av.agency_code ");
        String querySql = super.boxUpSql(sb.toString());
        //请求参数初始化
        Object[] objects = super.boxUpParam(paramList.toArray(), paginationDTO);
        return super.getjdbcTemplate().queryForList(querySql,objects);
    }

    @Override
    public int selectVerifySalaryHisDataTotal( String whereSql,List<String> agencyIds,Map<String,Object> otherParams) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" count(*) ")
                .append(" from ").append(TableName.THIRTEEN_AGENCY_VERIFY).append(" av ")
                .append(" where is_end=? and av.is_deleted=? ").append(whereSql)
                .append(" and ").append(SysUtils.inSql("agency_id",agencyIds));//权限控制
        paramList.add(SystemDictConstant.IS_TRUE);
        paramList.add(SystemDictConstant.NOT_DELETED);
        if(!SysUtils.isNullOrEmpty(otherParams.get("bank"))){
            sb.append(" and agency_id in ( select distinct agency_id from ")
                    .append(TableName.SALARY_THIRTEEN_MONTH_HIS)
                    .append(" where sala_card_bank=? and mof_div_code=? and salary_year=? )");
            paramList.add(otherParams.get("bank"));
            paramList.add(otherParams.get("province"));
            paramList.add(otherParams.get("year"));
        }
        if(!SysUtils.isNullOrEmpty(otherParams.get("province"))){
            sb.append(" and av.mof_div_code=? ");
            paramList.add(otherParams.get("province"));
        }
        if(!SysUtils.isNullOrEmpty(otherParams.get("is_pay_data_created"))){
            sb.append(" and av.is_pay_data_created=? ");
            paramList.add(otherParams.get("is_pay_data_created"));
        }
        if(!SysUtils.isNullOrEmpty(otherParams.get("year"))){
            sb.append(" and av.salary_year=? ");
            paramList.add(otherParams.get("year"));
        }
        return  this.getJdbcTemplate().queryForObject(sb.toString(),paramList.toArray(), Integer.class);
    }

    @Override
    public void deleteSalaryThirteenMonthByAgency(List<String> agencyId) {
        if(!SysUtils.isNullOrEmpty(agencyId)) {
            StringBuilder sql = new StringBuilder();
            sql.append(" delete from ").append(TableName.SALARY_THIRTEEN_MONTH).append(" where ").append(SysUtils.inSql("agency_id", agencyId));
            super.getJdbcTemplate().update(sql.toString());
        }
    }

    @Override
    public void deleteSalaryThirteenBeginByAgency(List<String> agencyId,String salary_year) {
        if(!SysUtils.isNullOrEmpty(agencyId)) {
            StringBuilder sql = new StringBuilder();
            sql.append(" delete from ").append(TableName.SALARY_THIRTEEN_BEGIN).append(" where ").append(SysUtils.inSql("agency_id", agencyId)).append(" and salary_year=?");
            super.getJdbcTemplate().update(sql.toString(),salary_year);
        }
    }

    @Override
    public void batchInsertSalaryThirteenHis(List<SalaryThirteenMonth> list,String salary_year) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        String time=String.valueOf(DateUtils.getCurrentTimeStamp());
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.SALARY_THIRTEEN_MONTH_HIS);
        sql.append("(id, create_time, update_time, agency_id, person_id, mof_div_code, ")
                .append(" person_name, iden_no, sala_card_no, sala_card_bank, percent, is_deleted, merit_pay_code,salary_year ")
                .append(" , s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, s39, s40, s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, s57, s58, s59, s60, s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, s75, s76, s77, s78, s79, s80, s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, s93, s94, s95, s96, s97, s98, s99, s100)");
        sql.append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        List<Object[]> paramList = new ArrayList<>(list.size());
        for(SalaryThirteenMonth stm : list){
            List<Object> param=new ArrayList<>();
            param.add(SysUtils.generateGuid());
            param.add(time);
            param.add(time);
            param.add(stm.getAgencyId());
            param.add(stm.getPersonId());
            param.add(stm.getMofDivCode());
            param.add(stm.getPersonName());
            param.add(stm.getIdenNo());
            param.add(stm.getSalaCardNo());
            param.add(stm.getSalaCardBank());
            param.add(stm.getPercent());
            param.add(SystemDictConstant.NOT_DELETED);
            param.add(stm.getMeritPayCode());
            param.add(salary_year);
            for (int i=1;i<=100;i++) {
                Class<?> clazz = stm.getClass();
                Method getMethod = clazz.getDeclaredMethod("getS" +i
                );
                Object ob=SysUtils.isNotNullOrEmpty(getMethod.invoke(stm))?getMethod.invoke(stm):"0";
                param.add(ob);
            }

            paramList.add(param.toArray());
        }

        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramList.get(i);
                for(int a = 0; a < sme.length; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public List<SalaryThirteenMonth> selectSalaryThirteenMonthHis(List<String> agencyId, String salary_year) {
        List<Object> param=new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select  ").append("id, create_time, update_time, agency_id, person_id, mof_div_code, ")
                .append(" person_name, iden_no, sala_card_no, sala_card_bank, percent, is_deleted, merit_pay_code ")
                .append(" , s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, s39, s40, s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, s57, s58, s59, s60, s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, s75, s76, s77, s78, s79, s80, s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, s93, s94, s95, s96, s97, s98, s99, s100 ");
        sql.append(" from ").append(TableName.SALARY_THIRTEEN_MONTH_HIS)
                .append(" where ").append(SysUtils.inSql(" agency_id ",agencyId))
                .append(" and salary_year = ?  ");
        param.add(salary_year);
        return this.getJdbcTemplate().query(sql.toString(),new BeanPropertyRowMapper(SalaryThirteenMonth.class),param.toArray());
    }

    @Override
    public void deleteSalaryThirteenMonthHisByAgencys(List<String> agencyIds,String salary_year) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete from ").append(TableName.SALARY_THIRTEEN_MONTH_HIS).append(" where salary_year=? and ").append(SysUtils.inSql("agency_id",agencyIds));
        super.getJdbcTemplate().update(sql.toString(),salary_year);
    }

    @Override
    public Map<String, Object> queryAgencyTotal(String agencyId) {
        StringBuffer sb=new StringBuffer();
        sb.append("select count(*) personCount,sum(s1) reallySendSalarySum from ").append(TableName.SALARY_THIRTEEN_MONTH)
                .append(" where agency_id=? ");
        Object[] objects=new Object[]{
                agencyId
        };
        return  this.getJdbcTemplate().queryForMap(sb.toString(),objects);
    }

    @Override
    public Map<String, Object> queryAgencyBeginTotal(String agencyId) {
        StringBuffer sb=new StringBuffer();
        sb.append("select count(*) beginPersonCount,sum(s1) beginReal from ").append(TableName.SALARY_THIRTEEN_BEGIN)
                .append(" where agency_id=? ");
        Object[] objects=new Object[]{
                agencyId
        };
        return  this.getJdbcTemplate().queryForMap(sb.toString(),objects);
    }

    @Override
    public Integer queryAgencyAddCount(String agencyId) {
        StringBuffer sb=new StringBuffer();
        sb.append("select count(*) from ").append(TableName.SALARY_THIRTEEN_MONTH).append(" a left join ")
                .append(TableName.SALARY_THIRTEEN_BEGIN).append(" b on a.person_id=b.person_id and a.agency_id=b.agency_id ")
                .append(" where a.agency_id=? and b.id is null");
        Object[] objects=new Object[]{
                agencyId
        };
        return  this.getJdbcTemplate().queryForObject(sb.toString(),objects, Integer.class);
    }

    @Override
    public Integer queryAgencyDelCount(String agencyId) {
        StringBuffer sb=new StringBuffer();
        sb.append("select count(*) from ").append(TableName.SALARY_THIRTEEN_MONTH).append(" a right join ")
                .append(TableName.SALARY_THIRTEEN_BEGIN).append(" b on a.person_id=b.person_id and a.agency_id=b.agency_id ")
                .append(" where b.agency_id=? and a.id is null");
        Object[] objects=new Object[]{
                agencyId
        };
        return  this.getJdbcTemplate().queryForObject(sb.toString(),objects, Integer.class);
    }

    @Override
    public List<Map<String,Object>>  queryAgencyAddPersonInfo(String agencyId) {
        StringBuffer sb=new StringBuffer();
        sb.append("select '增加人员' status,a.person_name,a.iden_no,a.sala_card_bank,a.sala_card_no,a.s1 from ").append(TableName.SALARY_THIRTEEN_MONTH).append(" a left join ")
                .append(TableName.SALARY_THIRTEEN_BEGIN).append(" b on a.person_id=b.person_id and a.agency_id=b.agency_id ")
                .append(" where a.agency_id=? and b.id is null");
        Object[] objects=new Object[]{
                agencyId
        };
        return  this.getJdbcTemplate().queryForList(sb.toString(),objects);
    }

    @Override
    public List<Map<String,Object>> queryAgencyDelPersonInfo(String agencyId) {
        StringBuffer sb=new StringBuffer();
        sb.append("select '减少人员' status,b.person_name,b.iden_no,b.sala_card_bank,b.sala_card_no,b.s1  from ").append(TableName.SALARY_THIRTEEN_MONTH).append(" a right join ")
                .append(TableName.SALARY_THIRTEEN_BEGIN).append(" b on a.person_id=b.person_id and a.agency_id=b.agency_id ")
                .append(" where b.agency_id=? and a.id is null");
        Object[] objects=new Object[]{
                agencyId
        };
        return  this.getJdbcTemplate().queryForList(sb.toString(),objects);
    }
}
